#!/usr/bin/env python
# -*- coding:utf-8 -*-

# file:do_exam1.py
# author:王雯雯
# datetime:2024-12-8
# software: PyCharm

import pymysql

# 配置数据库连接参数，根据实际情况修改用户名、密码、主机和数据库名
config = {
    'host': 'localhost',
    'user': 'your_username',
    'password': 'your_password',
    'database':'message_board'
}

# 插入留言信息
def insert_message(msg_content, msg_author, review_status):
    try:
        connection = pymysql.connect(**config)
        cursor = connection.cursor()
        sql = "INSERT INTO message_table (msg_content, msg_author, review_status) VALUES (%s, %s, %s)"
        cursor.execute(sql, (msg_content, msg_author, review_status))
        connection.commit()
        connection.close()
        print("留言信息插入成功")
    except pymysql.Error as e:
        print(f"插入留言信息时出错: {e}")


message_content = "这是一条新的留言内容示例"
message_author = "John Doe"
review_status = "待审核"
insert_message(message_content, message_author, review_status)


# 根据留言ID删除留言信息
def delete_message(msg_id):
    try:
        connection = pymysql.connect(**config)
        cursor = connection.cursor()
        sql = "DELETE FROM message_table WHERE msg_id = %s"
        cursor.execute(sql, (msg_id,))
        connection.commit()
        connection.close()
        print("留言信息删除成功")
    except pymysql.Error as e:
        print(f"删除留言信息时出错: {e}")
message_content = "这是一条新的留言内容示例"
message_author = "John Doe"
review_status = "待审核"
insert_message(message_content, message_author, review_status)

# 根据留言ID更新留言内容（可按需扩展更新其他字段）
def update_message(msg_id, new_content):
    try:
        connection = pymysql.connect(**config)
        cursor = connection.cursor()
        sql = "UPDATE message_table SET msg_content = %s WHERE msg_id = %s"
        cursor.execute(sql, (new_content, msg_id))
        connection.commit()
        connection.close()
        print("留言信息更新成功")
    except pymysql.Error as e:
        print(f"更新留言信息时出错: {e}")

new_content = "更新后的留言内容示例"
update_message(2, new_content)

# 查询所有留言信息
def query_messages():
    try:
        connection = pymysql.connect(**config)
        cursor = connection.cursor()
        sql = "SELECT * FROM message_table"
        cursor.execute(sql)
        results = cursor.fetchall()
        for row in results:
            print(f"留言ID: {row[0]}, 内容: {row[1]}, 留言人: {row[2]}, 审核状态: {row[3]}, 留言时间: {row[4]}")
        connection.close()
    except pymysql.Error as e:
        print(f"查询留言信息时出错: {e}")
query_messages()

